11: Visualizations

import warnings
warnings.filterwarnings('ignore')

import pandas as pd
import numpy as np
import pydeck as pdk
import plotly.express as px
from sodapy import Socrata
import pandas as pd
# import pandahelper.reports as ph
from datetime import datetime
import urllib
import os
dateparse = lambda x: pd.to_datetime(x, format='%Y-%m-%d %H:%M:%S')
data1 = pd.read_csv('output/datasets/dataset_cleaned.csv', parse_dates=['date/time'], date_parser=dateparse)

# Check the dtype to ensure conversion
print(data1['date/time'].dtype)

datetime64[ns]

import pandas as pd

data=data1.copy()
try:
    data.loc[:, 'date/time'] = pd.to_datetime(data['crash_date'].astype(str) + ' ' + data['crash_time'].astype(str))
    print("Datetime conversion successful")
except Exception as e:
    print("Datetime conversion failed:", e)

# Define collision categories explicitly with .loc
data.loc[:, 'total_collisions'] = 1
data.loc[:, 'serious_collisions'] = ((data['number_of_persons_injured'] > 0) | (data['number_of_persons_killed'] > 0)).astype(int)
data.loc[:, 'collisions_with_pedestrians_cyclists'] = ((data['number_of_pedestrians_injured'] > 0) |
                                                      (data['number_of_pedestrians_killed'] > 0) |
                                                      (data['number_of_cyclist_injured'] > 0) |
                                                      (data['number_of_cyclist_killed'] > 0)).astype(int)

# Aggregate data for different time periods
data.loc[:, 'year'] = data['date/time'].dt.year
data.loc[:, 'month_year'] = data['date/time'].dt.to_period('M').astype(str)
data.loc[:, 'week_year'] = data['date/time'].dt.to_period('W').astype(str)
data.loc[:, 'day_year'] = data['date/time'].dt.to_period('D').astype(str)

# Group and aggregate data
data_yearly = data.groupby('year').agg({
    'total_collisions': 'sum',
    'serious_collisions': 'sum',
    'collisions_with_pedestrians_cyclists': 'sum'
}).reset_index()

data_monthly = data.groupby('month_year').agg({
    'total_collisions': 'sum',
    'serious_collisions': 'sum',
    'collisions_with_pedestrians_cyclists': 'sum'
}).reset_index()

data_weekly = data.groupby('week_year').agg({
    'total_collisions': 'sum',
    'serious_collisions': 'sum',
    'collisions_with_pedestrians_cyclists': 'sum'
}).reset_index()

data_daily = data.groupby('day_year').agg({
    'total_collisions': 'sum',
    'serious_collisions': 'sum',
    'collisions_with_pedestrians_cyclists': 'sum'
}).reset_index()

Datetime conversion successful

import plotly.graph_objects as go

# Function to create a plot with customizations
def create_plot():
    # Create an empty figure
    fig = go.Figure()

    # Define initial data (yearly data as default)
    initial_data = data_yearly

    # Add traces for each type of collision
    fig.add_trace(go.Bar(
        x=initial_data['year'], y=initial_data['total_collisions'],
        name='Total Collisions',
        marker_color='rgba(55, 83, 109, 0.8)',  # dark blue
        text=initial_data['total_collisions'],  # Add text on bars
        textposition='outside'  # Position text outside of bars
    ))
    fig.add_trace(go.Bar(
        x=initial_data['year'], y=initial_data['serious_collisions'],
        name='Serious Collisions',
        marker_color='rgba(255, 133, 27, 0.8)',  # bright orange
        text=initial_data['serious_collisions'],  # Add text on bars
        textposition='outside'  # Position text outside of bars
    ))
    fig.add_trace(go.Bar(
        x=initial_data['year'], y=initial_data['collisions_with_pedestrians_cyclists'],
        name='Collisions with Pedestrians and Cyclists',
        marker_color='rgba(50, 171, 96, 0.8)',  # green
        text=initial_data['collisions_with_pedestrians_cyclists'],  # Add text on bars
        textposition='outside'  # Position text outside of bars
    ))

    # Update layout with dropdown
    fig.update_layout(
        title='Traffic Collision Data - Yearly View',
        xaxis_title='Year',
        yaxis_title='Number of Collisions',
        barmode='group',
        legend_title='Types of Collisions',
        plot_bgcolor='white',
        width=1200,
        height=600,
        updatemenus=[
            dict(
                buttons=list([
                    dict(args=[{'x': [data_yearly['year'].astype(str)], 'y': [data_yearly[col] for col in ['total_collisions', 'serious_collisions', 'collisions_with_pedestrians_cyclists']],
                               'text': [data_yearly[col] for col in ['total_collisions', 'serious_collisions', 'collisions_with_pedestrians_cyclists']]}],
                         label="Yearly",
                         method="restyle"),
                    dict(args=[{'x': [data_monthly['month_year']], 'y': [data_monthly[col] for col in ['total_collisions', 'serious_collisions', 'collisions_with_pedestrians_cyclists']],
                               'text': [data_monthly[col] for col in ['total_collisions', 'serious_collisions', 'collisions_with_pedestrians_cyclists']]}],
                         label="Monthly",
                         method="restyle"),
                    dict(args=[{'x': [data_weekly['week_year']], 'y': [data_weekly[col] for col in ['total_collisions', 'serious_collisions', 'collisions_with_pedestrians_cyclists']],
                               'text': [data_weekly[col] for col in ['total_collisions', 'serious_collisions', 'collisions_with_pedestrians_cyclists']]}],
                         label="Weekly",
                         method="restyle"),
                    dict(args=[{'x': [data_daily['day_year']], 'y': [data_daily[col] for col in ['total_collisions', 'serious_collisions', 'collisions_with_pedestrians_cyclists']],
                               'text': [data_daily[col] for col in ['total_collisions', 'serious_collisions', 'collisions_with_pedestrians_cyclists']]}],
                         label="Daily",
                         method="restyle")
                ]),
                direction="down",
                pad={"r": 10, "t": 10},
                showactive=True,
                x=0.1,
                xanchor="left",
                y=1.15,
                yanchor="top"
            ),
        ]
    )

    return fig
fig = create_plot()
IMG_DIR = "output/trends"
os.makedirs(IMG_DIR, exist_ok=True)
fig.write_html(os.path.join(IMG_DIR, "dataset_vis.html"))
# Generate and show the plot


fig.show()

Monthly Collision Data Across All Years

import pandas as pd
import plotly.express as px

# Load the data
data = data1.copy()

# Convert 'date/time' to datetime if necessary
data['date/time'] = pd.to_datetime(data['date/time'], errors='coerce')

# Ensure the additional columns are in place
data['total_collisions'] = 1
data['serious_collisions'] = ((data['number_of_persons_injured'] > 0) | (data['number_of_persons_killed'] > 0)).astype(int)
data['collisions_with_pedestrians_cyclists'] = ((data['number_of_pedestrians_injured'] > 0) |
                                                (data['number_of_pedestrians_killed'] > 0) |
                                                (data['number_of_cyclist_injured'] > 0) |
                                                (data['number_of_cyclist_killed'] > 0)).astype(int)
# Extract month from 'date/time' for grouping
data['month'] = data['date/time'].dt.month

# Group by month and sum the collisions
monthly_data = data.groupby('month').agg({
    'total_collisions': 'sum',
    'serious_collisions': 'sum',
    'collisions_with_pedestrians_cyclists': 'sum'
}).reset_index()

# Convert 'month' from numbers to names for better readability
import calendar
monthly_data['month'] = monthly_data['month'].apply(lambda x: calendar.month_abbr[x])

# Define color map for the different collision types
color_map = {
    'total_collisions': 'pink',  # yellow for total collisions
    'serious_collisions': 'red',  # Red for serious collisions
    'collisions_with_pedestrians_cyclists': 'green'  # Green for collisions involving pedestrians/cyclists
}

import plotly.express as px

fig = px.bar(monthly_data, 
             x='month', 
             y=['total_collisions', 'serious_collisions', 'collisions_with_pedestrians_cyclists'],
             barmode='group', 
             title='Monthly Collision Data Across All Years',
             color_discrete_map=color_map)

fig.update_layout(
    xaxis_title='Month',
    yaxis_title='Number of Collisions',
    legend_title='Collision Type'
)
IMG_DIR = "output/trends"
os.makedirs(IMG_DIR, exist_ok=True)
fig.write_html(os.path.join(IMG_DIR, "monthly_collision.html"))
fig.show()

Weekly Collision Data

import pandas as pd
import plotly.express as px


# Extract day of the week from 'date/time' (0=Monday, 6=Sunday)
data['day_of_week'] = data['date/time'].dt.dayofweek

# Map integers to day names for better readability
day_names = {0: 'Monday', 1: 'Tuesday', 2: 'Wednesday', 3: 'Thursday', 4: 'Friday', 5: 'Saturday', 6: 'Sunday'}
data['day_of_week'] = data['day_of_week'].map(day_names)

# Group by day of the week and sum the collisions
weekly_data = data.groupby('day_of_week').agg({
    'total_collisions': 'sum',
    'serious_collisions': 'sum',
    'collisions_with_pedestrians_cyclists': 'sum'
}).reset_index()

# To ensure the days are in the correct order
weekly_data['day_of_week'] = pd.Categorical(weekly_data['day_of_week'], categories=day_names.values(), ordered=True)
weekly_data.sort_values('day_of_week', inplace=True)

# Define color map for the different collision types
color_map = {
    'total_collisions': 'pink',  # pink for total collisions
    'serious_collisions': 'red',  # Red for serious collisions
    'collisions_with_pedestrians_cyclists': 'green'  # Green for collisions involving pedestrians/cyclists
}

fig = px.bar(weekly_data, 
             x='day_of_week', 
             y=['total_collisions', 'serious_collisions', 'collisions_with_pedestrians_cyclists'],
             barmode='group', 
             title='Weekly Collision Data',
             color_discrete_map=color_map)

fig.update_layout(
    xaxis_title='Day of the Week',
    yaxis_title='Number of Collisions',
    legend_title='Collision Type'
)
IMG_DIR = "output/trends"
os.makedirs(IMG_DIR, exist_ok=True)
fig.write_html(os.path.join(IMG_DIR, "weekly_collision.html"))

fig.show()

Hourly Collisions

import pandas as pd
import plotly.express as px

# Ensure 'date/time' is a datetime type
data['date/time'] = pd.to_datetime(data['date/time'], errors='coerce')

# Extract the hour from 'date/time'
data['hour_of_day'] = data['date/time'].dt.hour

# Group by hour of day and sum the collision types
hourly_data = data.groupby('hour_of_day').agg({
    'total_collisions': 'sum',
    'serious_collisions': 'sum',
    'collisions_with_pedestrians_cyclists': 'sum'
}).reset_index()

# Ensure hours are sorted (though they should be from the groupby)
hourly_data.sort_values('hour_of_day', inplace=True)
# Define color map for the different collision types
color_map = {
    'total_collisions': 'navy',  # Navy for total collisions
    'serious_collisions': 'crimson',  # Crimson for serious collisions
    'collisions_with_pedestrians_cyclists': 'forestgreen'  # Forest green for collisions with pedestrians/cyclists
}

fig = px.bar(hourly_data, 
             x='hour_of_day', 
             y=['total_collisions', 'serious_collisions', 'collisions_with_pedestrians_cyclists'],
             barmode='group', 
             title='Hourly Collision Data Across All Days',
             color_discrete_map=color_map)

fig.update_layout(
    xaxis_title='Hour of the Day',
    xaxis=dict(tickmode='linear', tick0=0, dtick=1),  # Ensure hourly ticks
    yaxis_title='Number of Collisions',
    legend_title='Collision Type'
)
IMG_DIR = "output/trends"
os.makedirs(IMG_DIR, exist_ok=True)
fig.write_html(os.path.join(IMG_DIR, "hourly_collision.html"))
fig.show()

Across Seasons

import pandas as pd
import plotly.express as px

def month_to_season(month):
    if 3 <= month <= 5:
        return 'Spring'
    elif 6 <= month <= 8:
        return 'Summer'
    elif 9 <= month <= 11:
        return 'Fall'
    else:
        return 'Winter'  # December to February

# Assuming data has been loaded and 'date/time' converted to datetime
data['date/time'] = pd.to_datetime(data['date/time'], errors='coerce')

# Create a 'Season' column based on the month of 'date/time'
data['Season'] = data['date/time'].dt.month.apply(month_to_season)

# Group by 'Season' and aggregate collision data
seasonal_data = data.groupby('Season').agg({
    'total_collisions': 'sum',
    'serious_collisions': 'sum',
    'collisions_with_pedestrians_cyclists': 'sum'
}).reset_index()

# Order the seasons logically for presentation
season_order = ['Winter', 'Spring', 'Summer', 'Fall']
seasonal_data['Season'] = pd.Categorical(seasonal_data['Season'], categories=season_order, ordered=True)
seasonal_data = seasonal_data.sort_values('Season')

# Define color map for the different collision types
color_map = {
    'total_collisions': 'navy',
    'serious_collisions': 'crimson',
    'collisions_with_pedestrians_cyclists': 'forestgreen'
}

# Plotting with custom colors
fig = px.bar(seasonal_data, 
             x='Season', 
             y=['total_collisions', 'serious_collisions', 'collisions_with_pedestrians_cyclists'],
             barmode='group', 
             title='Seasonal Collision Data Across All Years',
             color_discrete_map=color_map)

fig.update_layout(
    xaxis_title='Season',
    yaxis_title='Number of Collisions',
    legend_title='Collision Type'
)
IMG_DIR = "output/trends"
os.makedirs(IMG_DIR, exist_ok=True)
fig.write_html(os.path.join(IMG_DIR, "season_collision.html"))
fig.show()

Dangerous Single Points

MOST_SERIOUS = 250 # number of most dangerous locations to identify FATALITY_MULTIPLE = 10 # weight assigned to fatalities relative to injuries when we select 250 of the most dangerous locations

The worst single points in NYC are: Where West Fordam Road crosses the Major Deegan Expressway in the Bronx Two coordinates on the Verrazano Bridge A single coordinate on the Whitestone Bridge The intersection of Guy R. Brewer Blvd. with Rockaway Blvd. next to Kennedy Airport Where East 138th Street intersects Bruckner Blvd. and the Bruckner Expressway in the Bronx

Several highways stand out as having multiple single coordinates with high numbers of injuries and deaths. For example: The Belt Parkway near Kennedy Airport in Queens The Bruckner Expressway in the Bronx The Cross Bronx Expressway between the Alexander Hamilton Bridge and 3rd Ave (in the Bronx)

Several non-highway roadways stand out as having multiple dangerous single coordinates. For example: Eastern Parkway in Brooklyn Utica Avenue in Brooklyn Woodhaven Blvd. in Queens Fordham Road in the Bronx 125th Street in Manhattan

import pandas as pd
import folium
import os

data = data.copy()
# Assuming 'data' is your DataFrame and is already loaded
# Convert latitude and longitude columns to numeric types
# Convert latitude and longitude columns to numeric types safely
data.loc[:, 'latitude'] = pd.to_numeric(data['latitude'], errors='coerce')
data.loc[:, 'longitude'] = pd.to_numeric(data['longitude'], errors='coerce')

# Sum up injuries and fatalities using .loc for safe assignment
data.loc[:, 'injured'] = data['number_of_persons_injured'] + data['number_of_pedestrians_injured'] + \
                         data['number_of_cyclist_injured'] + data['number_of_motorist_injured']
data.loc[:, 'killed'] = data['number_of_persons_killed'] + data['number_of_pedestrians_killed'] + \
                        data['number_of_cyclist_killed'] + data['number_of_motorist_killed']

# Proceed with the rest of your code


MOST_SERIOUS = 250  # number of most dangerous locations to identify
FATALITY_MULTIPLE = 10  # weight assigned to fatalities relative to injuries



# Group data by location and sum the results
dangerous = data.groupby(['latitude', 'longitude'])[['injured', 'killed']].sum()
dangerous['danger'] = dangerous['injured'] + (dangerous['killed'] * FATALITY_MULTIPLE)
dangerous = dangerous.reset_index()
most_dangerous = dangerous.sort_values(by="danger", ascending=False).head(MOST_SERIOUS)

# Create Folium map
NYC_MAP_CENTER = (40.73, -73.92)  # center of the map
danger_map = folium.Map(location=NYC_MAP_CENTER, zoom_start=10, tiles='OpenStreetMap')

# Add markers to the map
for index, row in most_dangerous.iterrows():
    info_str = f"INJURED: {int(row['injured'])}\nKILLED: {int(row['killed'])}"
    folium.Marker(
        location=(row['latitude'], row['longitude']),
        icon=folium.Icon(color="red", icon="fa-exclamation-triangle", prefix='fa'),
        tooltip=info_str,
        popup=info_str
    ).add_to(danger_map)

# Ensure the output directory exists
IMG_DIR = "output/hotspots"
os.makedirs(IMG_DIR, exist_ok=True)
danger_map.save(os.path.join(IMG_DIR, "points_serious_map.html"))

# If running in a Jupyter notebook, display the map
danger_map